package telsoft.app.bean;

import java.sql.*;
import java.util.*;
import telsoft.admin.util.*;
import telsoft.sql.*;
import telsoft.app.util.*;
import telsoft.util.*;

/**
 * <p>Title: </p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2009</p>
 *
 * <p>Company: </p>
 *
 * @author not attributable
 * @version 1.0
 */
public class TransportBean extends AppEntityProcessor {
    private Vector vtData = new Vector();
    private Vector vtErrData2 = new Vector();
    private Vector vtRow = new Vector();
    private String mstrID = "";
    private String mstrID_map = "";
    public String mstrID_area = "";
    public String mstrIMSI = "";
    public String mstrStartdate = "";
    public String mstrFinishdate = "";
    private Integer isuccess = 0;
    PreparedStatement stmt = null;
    String strSQL;
    String SQL;
//    PreparedStatement stmt = null;

    public Vector loadForm() throws Exception {
        try {
            open();
            strSQL =
                    "select ISDN, IMSI,CUS_NAME, to_char(CUS_BIRTHDAY,'dd/mm/yyyy'), CUS_ADDRESS, to_char(REG_DATE,'dd/mm/yyyy HH24:MI:SS') from CUSTOMER_REG ";
            //khi load file gio kich hoat thi them HH24:MI:SSxFF
            Vector vtReturn = Database.executeQuery(getConnection(), strSQL);
            return vtReturn; //tra ve vector de day ve client
        } finally {
            close();
        }
    }

    public Vector loadForm2() throws Exception {
        try {
            open();
            SQL =
                    "select id_map,area.fullname, area_map_isdn.imsi_prefix,to_char(area_map_isdn.start_date,'dd/mm/yyyy'),to_char(area_map_isdn.finish_date,'dd/mm/yyyy'),area.area_id" +
                    " from area_map_isdn, area where area.area_id=area_map_isdn.area_id";
            Vector vtReturn2 = Database.executeQuery(getConnection(), SQL);
            return vtReturn2; //tra ve vector de day ve client
        } finally {
            close();
        }
    }
    public Vector getnumber() throws Exception {
	    try {
		open();
		strSQL = " select b.isdn,b.cus_name,b.cus_address,b.reg_date, "+
			 " case "+
			 " when b.reg_status ='1' then 'Đã kích hoạt' "+
			 " else 'Chưa kích hoạt' "+
			 " end "+
			" from area a, isdn b, area_map_isdn c where a.area_id=c.area_id and c.area_id=? and b.imsi like c.imsi_prefix ";
//	    loadParameter();
		stmt = mcnMain.prepareStatement(strSQL);
		stmt.setString(1, mstrID_area);
		return Database.convertToVector(stmt.executeQuery());
	    } finally {
		close();
	    }
    }
    public Vector FillDialog() throws Exception {
        try {
            open();
            String SQL =
                    "select area_id, fullname from area where parent_code<>'0'";
            Vector vtData = Database.executeQuery(getConnection(), SQL);
            return vtData;
        } finally {
            close();
        }
    }

    public void Refresh() throws Exception {
	PreparedStatement stmt = null;
        String strSQL =
	    " update isdn set cus_name=(select cus_name from customer_reg where isdn=isdn.isdn), "+
		    " cus_birthday=(select cus_birthday from customer_reg where isdn=isdn.isdn), "+
		    " cus_address=(select cus_address from customer_reg where isdn=isdn.isdn), "+
		     " reg_date=(select reg_date from customer_reg where isdn=isdn.isdn), "+
		     " reg_status=(case when EXISTS(select * FROM customer_reg where isdn=isdn.isdn) then '1' "+
		     " else '0'"+
			 " end)";
//		     " WHERE isdn=isdn.isdn ";

//                " UPDATE isdn from customer_reg SET " +
//                "  isdn.cus_name=customer_reg.cus_name,isdn.cus_birthday=to_date(customer_reg.cus_birthday,'dd/mm/yyyy')," +
//                " isdn.cus_address=customer_reg.cus_address,isdn.reg_date=to_date(customer_reg.reg_date,'dd/mm/yyyy') " +
//                " WHERE isdn.ISDN=customer_reg.ISDN AND isdn.IMSI=customer_reg.IMSI ";
//	    "UPDATE a SET a.reg_status='1',"+
//	    " a.cus_name=b.cus_name, a.cus_birthday=to_date(b.cus_birthday,'dd/mm/yyyy'),"+
//	    " a.cus_address=b.cus_address,a.reg_date=to_date(b.reg_date,'dd/mm/yyyy') "+
//	    " FROM isdn a"+
//	     "JOIN customer_reg ON a.ISDN = b.ISDN ";
//	    "UPDATE isdn SET cus_name = customer_reg.cus_name,cus_birthday=to_date(customer_reg.cus_birthday,'dd/mm/yyyy'),"+
//            "cus_address=customer_reg.cus_address,reg_date=to_date(customer_reg.reg_date,'dd/mm/yyyy')"+
//            "FROM isdn, customer_reg"+
//            "WHERE customer_reg.ISDN=isdn.ISDN";

	    try {
		open();
//		PreparedStatement stmt = getConnection().prepareStatement(
//                    strSQL);
		stmt = mcnMain.prepareStatement(strSQL);
                stmt.executeUpdate();
            }
	    catch(Exception e)
	    {
		e.printStackTrace();
		throw new AppException(e, "TransportRefresh.update");
	    }
	    finally {
	    Database.closeObject(stmt);
	    close();
	}

    }

    protected String getModuleName() {
        return PermissionApp.FormTransport;
    }

    public void loadParameter() { //ham lay ve cac gia tri nguoi dung nhap tren for
        vtData = request.getVector("mvtData"); //lay ve vector du lieu
        if (vtData == null) {
            vtData = new Vector();
        }
        mstrID_area = StringUtil.nvl(request.getString("mstrID_area"), "");
        mstrIMSI = StringUtil.nvl(request.getString("mstrIMSI"), "");
        mstrStartdate = StringUtil.nvl(request.getString("mstrStartdate"), "");
        mstrFinishdate = StringUtil.nvl(request.getString("mstrFinishdate"), "");
	mstrID_map = StringUtil.nvl(request.getString("mstrID_map"), "");
    }

    protected void onInsert() throws Exception {
        PreparedStatement stmt = null;
        String strSQL =
                " INSERT INTO area_map_isdn(id_map,area_ID,imsi_prefix,start_date,finish_date)" +
                " VALUES (?,?,?,to_date(?,'dd/mm/yyyy'),to_date(?,'dd/mm/yyyy'))";

        try {
            loadParameter();
            stmt = mcnMain.prepareStatement(strSQL);
            mstrID_map = Database.getSequenceValue(mcnMain, "seq_map");
            stmt.setString(1, mstrID_map);
            stmt.setString(2, mstrID_area);
            stmt.setString(3, mstrIMSI);
            stmt.setString(4, mstrStartdate);
            stmt.setString(5, mstrFinishdate);
            stmt.executeUpdate();

            log.logAfterInsert(mstrLogID, "area_map_isdn",
                               "id_map='" +
                               StringEscapeUtil.escapeSql(mstrID_map) + "'");
            storeParameter();
        } catch (Exception e) {
            e.printStackTrace();
            throw new AppException(e, "TransportBean.insert");
        } finally {
            Database.closeObject(stmt);
        }

    }

    public void onUpdate() throws Exception {
        String strSQL = "";
        try {
//	    loadParameter();

            strSQL = "UPDATE area_map_isdn SET " +
                     "area_id = ?,imsi_prefix=?, start_date = to_date(?,'dd/mm/yyyy'),finish_date = to_date(?,'dd/mm/yyyy') " +
                     "WHERE id_map = ?";

            Vector vtChangeID = log.logBeforeUpdate(mstrLogID,
                    "area_map_isdn",
                    "id_map='" +
                    StringEscapeUtil.escapeSql(
                            mstrID_map) +
                    "'");

            PreparedStatement stmt = getConnection().prepareStatement(
                    strSQL);
            stmt.setString(1, mstrID_area);
            stmt.setString(2, mstrIMSI);
            stmt.setString(3, mstrStartdate);
            stmt.setString(4, mstrFinishdate);
            stmt.setString(5, mstrID_map);
            stmt.executeUpdate();

            log.logAfterUpdate(vtChangeID);
        } catch (Exception e) {
            e.printStackTrace();
            throw new AppException(e, "Transport.update");
        } finally {
            Database.closeObject(stmt);
        }
    }

//    protected void onUpdate() throws Exception
//    {
//	//mcnMain = mgetConnection();
//	PreparedStatement stmt = null;
//	ResultSet rs = null;
//	try
//	{
//	    Vector vtChangeID = log.logBeforeUpdate(mstrLogID,"area_map_isdn","id_map='" + StringEscapeUtil.escapeSql(mstrPrimaryKey) + "'");
//	    String strSQL = " SELECT 1 FROM area WHERE ROWNUM<=1 AND" +
//			    " UPPER(area_code)=UPPER(?) AND area_id <> ?";
//	    stmt = mcnMain.prepareStatement(strSQL);
//	    stmt.setString(1,mstrAreaCode);
//	    stmt.setString(2,mstrPrimaryKey);
//	    rs = stmt.executeQuery();
//	    if(rs.next())
//	    {
//		throw new AppException("Mã khu vực đã tồn tại","AreaBean.update");
//	    }
//	    stmt.close();
//	    /////////////////////////////////////////////////////////
//	    strSQL = "UPDATE area SET area_code = ?,parent_code = ?," +
//		     " name = ?,fullname = ?,center_code = ?,status = ?" +
//		     " WHERE area_id = ?";
//	    stmt = mcnMain.prepareStatement(strSQL);
//	    stmt.setString(1,mstrAreaCode);
//	    stmt.setString(2,mstrParentCode);
//	    stmt.setString(3,mstrName);
//	    stmt.setString(4,mstrFullName);
//	    stmt.setString(5,mstrCenter);
//	    stmt.setString(6,mstrStatus);
//	    stmt.setString(7,mstrPrimaryKey);
//	    stmt.executeUpdate();
//	    log.logAfterUpdate(vtChangeID);
//	    AppAuthenticator.notifyAuthenticationChanged(mcnMain);
//	}
//	finally
//	{
//	    Database.closeObject(rs);
//	    Database.closeObject(stmt);
//	    //Database.closeObject(mcnMain);
//	}
//    }
    public void storeParameter() {
        super.storeParameter();
        response.setString("mstrID_map", mstrID_map);
        response.setString("mstrID_area", mstrID_area);
        response.setString("mstrIMSI", mstrIMSI);
        response.setString("mstrStartdate", mstrStartdate);
        response.setString("mstrFinishdate", mstrFinishdate);
    }

    public void importData() throws SQLException {
        try {
            open();
            mcnMain.setAutoCommit(false);
            boolean bCheckError = true;
            //insert du lieu
            loadParameter();
            for (int i = 0; i < vtData.size(); i++) {
                PreparedStatement stmt = null;
                Vector vtRow = (Vector) vtData.elementAt(i);
                Vector vtErrData = new Vector();
                String strSQL = "INSERT INTO CUSTOMER_REG " +
                                "(ID_REG,ISDN,IMSI,CUS_NAME,CUS_BIRTHDAY,CUS_ADDRESS,REG_DATE)" +
                                "VALUES (?,?,?,?,to_date(?,'dd/mm/yyyy'),?,to_date(?,'dd/mm/yyyy HH24:MI:SS')) ";

                try {
                    mstrID = Database.getSequenceValue(mcnMain, "seq_customer");
                    stmt = mcnMain.prepareStatement(strSQL);
                    stmt.setString(1, mstrID);
                    stmt.setString(2, vtRow.elementAt(0).toString());
                    stmt.setString(3, vtRow.elementAt(1).toString());
                    stmt.setString(4, vtRow.elementAt(2).toString());
                    stmt.setString(5, vtRow.elementAt(3).toString());
                    stmt.setString(6, vtRow.elementAt(4).toString());
                    stmt.setString(7, vtRow.elementAt(5).toString());
                    stmt.executeUpdate();
//		   log.logAfterInsert(mstrLogID,"CUSTOMER_REG","ID_REG='" + StringEscapeUtil.escapeSql(mstrID) + "'");
//		   AppAuthenticator.notifyAuthenticationChanged(mcnMain);
                    isuccess++;
                } catch (Exception e) {
                    vtErrData.addElement(e.getMessage());
                    vtErrData.addElement(i);
                    vtErrData.addElement(vtRow.elementAt(0).toString());
                    vtErrData.addElement(vtRow.elementAt(1).toString());
                    vtErrData.addElement(vtRow.elementAt(2).toString());
                    vtErrData.addElement(vtRow.elementAt(3).toString());
                    vtErrData.addElement(vtRow.elementAt(4).toString());
                    vtErrData.addElement(vtRow.elementAt(5).toString());
                    vtErrData2.addElement(vtErrData);
                    bCheckError = false;
                }
            }
            if (!bCheckError) {
                isuccess = 0;
                throw new Exception("Error occur : Rollback");
            }
            mcnMain.commit();
        } catch (Exception e) {
            mcnMain.rollback(); //xoa het cac tac vu vua thao tac neu co ban ghi loi xay ra
            e.printStackTrace();
        } finally {
            mcnMain.setAutoCommit(true);
            response.setInt("isuccess", isuccess);
            response.setVector("vtErrData2", vtErrData2);
            close();
        }
    }
}
